package cn.itcast.jsjwl.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jsjwl.domain.Message;
import cn.itcast.jsjwl.domain.Student;
import cn.itcast.jsjwl.utils.JDBCUtils;

public class MessageDao {

	public int findTotalRecordsByStuId(Student stu) throws SQLException {
		String sql="select count(*) from message where stuid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler(),stu.getStuid());
		return num.intValue();
	}

	public List<Message> findMessageWithPage(int startIndex, int pageSize,Student stu) throws SQLException {
		String sql="SELECT * FROM message  WHERE stuid=?  ORDER BY leveWordTime  DESC LIMIT ? , ? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Message>(Message.class),stu.getStuid(),startIndex,pageSize);
		 
	}

	public void addMessage(Message msg) throws SQLException {
		String sql="INSERT INTO message (content,leveWordTime,stuid) VALUES( ? , ? , ? );";
		Object[] params= {msg.getContent(),msg.getLeveWordTime(),msg.getStuid() };
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);
	}
	public int findTotalRecords() throws SQLException {
		String sql = "SELECT COUNT(*) FROM message";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}

	public List<Message> findMessagesWithPageByTeacher(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM message ORDER BY leveWordTime DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Message>(Message.class),startIndex,pageSize);
	}

	public void replayMessage(Message replaymsg,String id) throws SQLException {
		String sql = "update message set replay =? , replayTime = ? ,replayname=? where messageid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params = {replaymsg.getReplay(), replaymsg.getReplayTime(),replaymsg.getReplayname() ,id};
		qr.update(sql, params);
	}

	public Message findMessageBymessageid(String messageId) throws SQLException {
		String sql="SELECT * FROM message WHERE messageid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Message>(Message.class),messageId);
	}

	public List<Message> findMessageWithPageByAdmin(int startIndex, int pageSize) throws SQLException {
		String sql = "SELECT * FROM message ORDER BY leveWordTime DESC LIMIT ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Message>(Message.class),startIndex,pageSize);
	}

	public void messageDel(String mId) throws SQLException {
		String sql="delete FROM message WHERE messageid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,mId);
		
	}
}	
